#!/usr/bin/perl -w

BEGIN {
	push(@INC,"/home/modules/");
};
use strict;
use utf8;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Text::Iconv;
use Encode;
use DBI;
use Cwd 'abs_path';

abs_path($0)=~/^(.*[\\\/])/;
my $logpath="$1/logs/xls_import.log";
sub mywarn {
	open OUT,">>$logpath" or print STDERR "Could not write to $logpath\n";
	foreach (@_) {
		print OUT $_;
		print OUT "\n" unless $_=~/\n$/;
#		print $_;
#		print "\n" unless $_=~/\n$/;
	}
	close OUT;
}

sub mydie {
	mywarn(@_);
	exit(1);
}

{
	$SIG{__WARN__}=\&mywarn;
	$SIG{__DIE__}=\&mydie;
	$SIG{INT}=\&mydie;

	warn "\n\n".localtime()."\n";
	warn $0." ".join(" ",@ARGV)."\n";
	die "Wrong parameters" if  @ARGV<4;
	my ($login,$password,$database,$file,$generator,$fid)=@ARGV;
	$fid=0 unless $fid;
	
	my $dbh=DBI->connect("dbi:Pg:dbname=$database;host=127.0.0.1",$login,$password, {AutoCommit => 0});
	my $sth1=$dbh->prepare("insert into Tspreadsheets (name,generator,fid) values (?,?,?)");
	my $sth2=$dbh->prepare("insert into Tworksheets (spreadsheet_id,name) values (?,?)");
	my $sth3=$dbh->prepare("insert into Tcells (worksheet_id,x,y,value) values (?,?,?,?)");

	my $parser;
	my $workbook;
	my $xlsx;
	if ($file=~/\.xls$/) {
		$parser=Spreadsheet::ParseExcel->new();
		$workbook=$parser->parse($file);
		die $parser->error(), ".\n" unless defined $workbook;
		$xlsx=0;
	} elsif ($file=~/\.xlsx$/) {
		$xlsx=1;
		my $converter;# = Text::Iconv -> new ("utf-8", "windows-1251");
		$workbook=Spreadsheet::XLSX -> new ($file, $converter);
	} else {
		die "Unknown file format - $file";
	}

	$file=~/([^\/\\]+)$/;
	$sth1->execute($1,$generator,$fid);
	my $ss_id=$dbh->last_insert_id(undef,undef,"tspreadsheets",undef);
	for my $worksheet ( $workbook->worksheets() ) {	
		$sth2->execute($ss_id,$worksheet->get_name());
		my $ws_id=$dbh->last_insert_id(undef,undef,"tworksheets",undef);
		my ( $row_min, $row_max ) = $worksheet->row_range();
		my ( $col_min, $col_max ) = $worksheet->col_range();

		for my $row ( $row_min .. $row_max ) {
			for my $col ( $col_min .. $col_max ) {
				my $val;
				if ($xlsx==0) {
					my $cell = $worksheet->get_cell( $row, $col );
					next unless $cell;
					$val=$cell->value();
				} else {
					my $cell=$worksheet -> {Cells} [$row] [$col];
					next unless $cell;
					$val=$cell -> {Val};
				}
				$val=~s/\xa0/ /g unless $xlsx;
				$val=~s/^\s+//;
				$val=~s/\s+$//;
				$sth3->execute($ws_id,$col,$row,$val);
			}
		}
	}
	$dbh->commit();
}

